package vn.edu.saigontech.ncms.web.bookstore;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import vn.edu.saigontech.ncms.model.bookstore.ImportReceipt;
import vn.edu.saigontech.ncms.model.bookstore.Item;
import vn.edu.saigontech.ncms.model.bookstore.ItemTitle;
import vn.edu.saigontech.ncms.model.bookstore.ItemType;
import vn.edu.saigontech.ncms.model.bookstore.Order;
import vn.edu.saigontech.ncms.model.system.Parameter;
import vn.edu.saigontech.ncms.service.ItemTypeService;
import vn.edu.saigontech.ncms.service.OrderService;
import vn.edu.saigontech.ncms.service.ParameterService;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;
import vn.edu.saigontech.ncms.web.bookstore.report.DetailExport;
import vn.edu.saigontech.ncms.web.bookstore.report.DetailImport;
import vn.edu.saigontech.ncms.web.bookstore.report.DetailReport;
import vn.edu.saigontech.ncms.web.bookstore.report.ImportExportDetail;
import vn.edu.saigontech.ncms.web.bookstore.report.ImportExportReport;

@SuppressWarnings("serial")
public class BookstoreReportAction extends BaseAction {
	private int month;
	private int year;
	private String date;
	
	public String showDailyReportForm() {
		this.date = DateUtil.toString(DateUtil.newDate(), this.i18n("format.date"));
		return SUCCESS;
	}
	
	public String reportDetails() {
		Date fromDate = DateUtil.getStartDateOfMonth(month, year);
		Date toDate = DateUtil.getEndDateOfMonth(month, year);
		
		ImportExportReport report = new ImportExportReport(fromDate, toDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "List of Books and CD - " + month + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold18 = ExcelUtil.makeFont(workbook, "Times New Roman", 18, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			
			HSSFFont fontNormal12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			
			HSSFFont fontItalic12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			fontItalic12.setItalic(true);
			
			HSSFCellStyle styleCenter_B18 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold18);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			
			HSSFCellStyle styleCenter_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			HSSFCellStyle styleLeft_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			
			HSSFCellStyle styleCenter_I12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontItalic12);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B18);
			row.setHeightInPoints(28);
			cell.setCellValue(new HSSFRichTextString("DANH MỤC SÁCH & CD"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 6));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("Tháng " + month + "-" + year));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 6));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(30);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "STT", 256*5, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "Mã số", 256*13, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Tên sách/CD", 256*45, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "Sl. tồn đầu tháng", 256*7, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "Đvt", 256*9, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "Đơn giá", 256*9, styleCenter_B12);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "Ghi chú", 256*12, styleCenter_B12);
			
			int bookFirstRow = r + 2;
			List<ImportExportDetail> bookDetails = report.getBookReportDetails();
			int index = 1;
			for(int i = 0; i < bookDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(20);
				
				ImportExportDetail detail = bookDetails.get(i);
				Item item = detail.getItem();
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(index);
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemId()));
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getDisplayName()));
				cell.setCellStyle(styleLeft_N12);
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getFirstRemain());
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(item.getPrice().getAmount());
				cell.setCellStyle(styleCenter_N12);
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			int bookLastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Tổng số lượng sách tồn"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellFormula("SUM(D" + bookFirstRow + ":"  + "D" + bookLastRow + ")");
			
			int cdFirstRow = r + 2;
			List<ImportExportDetail> cdDetails = report.getCdReportDetails();
			for(int i = 0; i < cdDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(20);
				
				ImportExportDetail detail = cdDetails.get(i);
				Item item = detail.getItem();
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(index);
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemId()));
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getDisplayName()));
				cell.setCellStyle(styleLeft_N12);
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getFirstRemain());
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N12);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(item.getPrice().getAmount());
				cell.setCellStyle(styleCenter_N12);
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			int cdLastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Tổng số lượng đĩa tồn"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellFormula("SUM(D" + cdFirstRow + ":"  + "D" + cdLastRow + ")");
			
			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(String.format("Ngày %d Tháng %d Năm %d", 
					DateUtil.getDayOfMonth(toDate),
					DateUtil.getMonth(toDate) + 1,
					DateUtil.getYear(toDate))));
			cell.setCellStyle(styleCenter_I12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 5));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Người lập"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 5));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("(Ký, họ tên)"));
			cell.setCellStyle(styleCenter_I12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 5));
			
			r = r + 4;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Lê Thị Thanh Nhung"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 5));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch (IOException e) {
			e.printStackTrace();
		}
		return NONE;
	}
	
	public String reportMonthly() {
		Date fromDate = DateUtil.getStartDateOfMonth(month, year);
		Date toDate = DateUtil.getEndDateOfMonth(month, year);
		
		List<Order> orders = OrderService.INSTANCE.findByDateAsc(fromDate, toDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Monthly Report - " + month + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold16 = ExcelUtil.makeFont(workbook, "Times New Roman", 16, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont fontBold11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, true);
			
			HSSFFont fontNormal12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			
			HSSFCellStyle styleCenter_B16 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold16);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			HSSFCellStyle styleCenter_B11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold11);
			
			HSSFCellStyle styleRight_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			
			HSSFCellStyle styleCenter_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			HSSFCellStyle styleCenter_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleLeft_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleRight_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B16);
			row.setHeightInPoints(21);
			cell.setCellValue(new HSSFRichTextString("BẢNG KÊ THU TIỀN SÁCH ĐĨA ANH VĂN TỐI"));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("Cơ Sở: Quang Trung"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Tháng " + month + "/" + year));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(14);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "STT", 256*5, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "NGÀY", 256*11, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "SỐ BIÊN LAI", 256*16, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "HỌ TÊN", 256*30, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "LOẠI SÁCH", 256*23, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 2, 2));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 3, 3));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 4, 4));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 9, "GHI CHÚ", 256*14, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 10, "NGƯỜI THU", 256*12, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 9, 9));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 10, 10));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "SỐ TIỀN", 256*11, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 8));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(14);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "SB", 256*12, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "WB", 256*12, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "CD", 256*12, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 8, "TỔNG CỘNG", 256*11, styleCenter_B11);
			
			int firstRow = r + 2;
			
			int index = 1;
			for(Order order : orders) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);

				Date date = order.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(value));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(order.getNumber()));
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(order.getStudentName()));
				
				List<ItemTitle> titles = order.getItemTitles();
				StringBuffer buffer = new StringBuffer();
				for(int i = 0; i < titles.size(); i++) {
					if(i == (titles.size() - 1)) {
						buffer.append(titles.get(i).getTitle());
					} else
						buffer.append(titles.get(i).getTitle() + ", ");
				}
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(buffer.toString()));
				
				Map<ItemType, Double> map = order.getPriceMap();
				int acc = 5;
				
				Collection<ItemType> types = ItemTypeService.INSTANCE.findAll();
				for(ItemType type : types) {
					cell = row.createCell(acc, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleRight_N11);
					cell.setCellValue(map.get(type));
					cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
					acc++;
				}
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.setCellValue(order.getTotalPrice().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(order.getCashier().getFirstName()));
			}
			int lastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(18);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("TỔNG CỘNG"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 4));
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B12);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(F" + firstRow + ":"  + "F" + lastRow + ") + " + "SUM(G" + firstRow + ":"  + "G" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleRight_B12);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(H" + firstRow + ":"  + "H" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleRight_B12);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(I" + firstRow + ":"  + "I" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			r = r + 4;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString(String.format("SaigonTech ,ngày %d Tháng %d Năm %d", 
					DateUtil.getDayOfMonth(toDate),
					DateUtil.getMonth(toDate) + 1,
					DateUtil.getYear(toDate))));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 8, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Trưởng bộ phận"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Nhân viên"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 5));
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Kế Toán"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 8, 10));
			
			r = r + 5;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			String leader = ParameterService.INSTANCE.findText(Parameter.LEP);
			cell.setCellValue(new HSSFRichTextString(leader));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			String acc_bs_monthly = ParameterService.INSTANCE.findText(Parameter.ACC_BS_MONTHLY);
			cell.setCellValue(new HSSFRichTextString(acc_bs_monthly));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 5));
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			String accountant = ParameterService.INSTANCE.findText(Parameter.ACC);
			cell.setCellValue(new HSSFRichTextString(accountant));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 8, 10));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch(Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}
	
	public String reportImportExportDetails() {
		Date fromDate = DateUtil.getStartDateOfMonth(month, year);
		Date toDate = DateUtil.getEndDateOfMonth(month, year);
		
		DetailReport report = new DetailReport(fromDate, toDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Import-Export Details - " + this.month + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold16 = ExcelUtil.makeFont(workbook, "Times New Roman", 16, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont fontBold10 = ExcelUtil.makeFont(workbook, "Times New Roman", 10, true);
			
			HSSFFont fontNormal12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			
			HSSFFont fontBoldItalic12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			fontBoldItalic12.setItalic(true);
			
			HSSFFont fontItalic12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			fontItalic12.setItalic(true);
			
			HSSFFont fontItalic10 = ExcelUtil.makeFont(workbook, "Times New Roman", 10, false);
			fontItalic10.setItalic(true);
			
			HSSFCellStyle styleCenter_B16 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold16);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			HSSFCellStyle styleCenter_B10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold10);

			HSSFCellStyle styleCenter_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			HSSFCellStyle styleLeft_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			HSSFCellStyle styleLeft_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			HSSFCellStyle styleRight_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			HSSFCellStyle styleCenter_BI12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBoldItalic12);
			HSSFCellStyle styleCenter_I10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontItalic10);
			HSSFCellStyle styleCenter_I12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontItalic12);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B16);
			row.setHeightInPoints(21);
			cell.setCellValue(new HSSFRichTextString("SỐ CHI TIẾT NHẬP XUẤT"));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_BI12);
			cell.setCellValue(new HSSFRichTextString("Chương trình: Tiếng Anh buổi tối"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 12));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_BI12);
			cell.setCellValue(new HSSFRichTextString(String.format("Campus: Quang Trung")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 12));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(45);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "Loại", 256*6, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "Mã sách", 256*13, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Số PNX", 256*10, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "Ngày PNX", 256*10, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "Số chứng từ", 256*10, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "Ngày chứng từ", 256*10, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "Diễn giải", 256*24, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "SL Nhập", 256*8, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 8, "Đvt", 256*8, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 9, "SL Xuất", 256*8, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 10, "Giá bán", 256*11, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 11, "Thành tiền", 256*10, styleCenter_B10);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 12, "Ghi chú", 256*8, styleCenter_B10);
			
			int imFirstRow = r + 2;
			List<DetailImport> bookImportDetails = report.getBookImportDetails();
			for(int i = 0; i < bookImportDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				DetailImport detail = bookImportDetails.get(i);
				Item item = detail.getImportReceiptDetail().getItem();
				ImportReceipt receipt = detail.getImportReceipt();
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("N"));
				cell.setCellStyle(styleCenter_B10);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemId()));
				cell.setCellStyle(styleLeft_N12);
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(receipt.getNumber()));
				cell.setCellStyle(styleLeft_N11);
				
				Date date = receipt.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(value));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("Nhập bán cho học viên"));
				cell.setCellStyle(styleRight_N11);
				
				cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(detail.getImportReceiptDetail().getQuantity());
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N11);
			}
			
			List<DetailImport> cdImportDetails = report.getCdImportDetails();
			for(int i = 0; i < cdImportDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				DetailImport detail = cdImportDetails.get(i);
				Item item = detail.getImportReceiptDetail().getItem();
				ImportReceipt receipt = detail.getImportReceipt();
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("N"));
				cell.setCellStyle(styleCenter_B10);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemId()));
				cell.setCellStyle(styleLeft_N12);
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(receipt.getNumber()));
				cell.setCellStyle(styleLeft_N11);
				
				Date date = receipt.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(value));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("Nhập bán cho học viên"));
				cell.setCellStyle(styleRight_N11);
				
				cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(detail.getImportReceiptDetail().getQuantity());
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N11);
			}
			int imLastRow = r + 1;
			
			int exFirstRow = r + 2;
			List<DetailExport> bookExportDetails = report.getBookExportDetails();
			for(int i = 0; i < bookExportDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				DetailExport detailExport = bookExportDetails.get(i);
				Item item = detailExport.getOrderDetail().getItem();
				Order order = detailExport.getOrder();
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("X"));
				cell.setCellStyle(styleCenter_B10);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemId()));
				cell.setCellStyle(styleLeft_N12);
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(order.getNumber()));
				cell.setCellStyle(styleCenter_N11);
				
				Date date = order.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(value));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("Xuất bán cho học viên"));
				cell.setCellStyle(styleLeft_N11);
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(detailExport.getOrderDetail().getQuantity());
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				cell.setCellValue(detailExport.getOrderDetail().getPriceDetail().getAmount());
				
				cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				cell.setCellFormula("J" + (r + 1) + "*"  + "K" + (r + 1));
			}
			
			List<DetailExport> cdExportDetails = report.getCdExportDetails();
			for(int i = 0; i < cdExportDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				DetailExport detailExport = cdExportDetails.get(i);
				Item item = detailExport.getOrderDetail().getItem();
				Order order = detailExport.getOrder();
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("X"));
				cell.setCellStyle(styleCenter_B10);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemId()));
				cell.setCellStyle(styleLeft_N12);
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(order.getNumber()));
				cell.setCellStyle(styleCenter_N11);
				
				Date date = order.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(value));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString("Xuất bán cho học viên"));
				cell.setCellStyle(styleLeft_N11);
				
				cell = row.createCell(8, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(item.getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N11);
				
				cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(detailExport.getOrderDetail().getQuantity());
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				cell.setCellValue(detailExport.getOrderDetail().getPriceDetail().getAmount());
				
				cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				cell.setCellFormula("J" + (r + 1) + "*"  + "K" + (r + 1));
			}
			
			int exLastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(18);
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("CỘNG"));
			cell.setCellStyle(styleCenter_B10);
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B10);
			cell.setCellFormula("SUM(H" + imFirstRow + ":"  + "H" + imLastRow + ")");
			
			cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B10);
			cell.setCellFormula("SUM(J" + exFirstRow + ":"  + "J" + exLastRow + ")");
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B10);
			cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			cell.setCellFormula("SUM(L" + exFirstRow + ":"  + "L" + exLastRow + ")");
			
			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(String.format("Ngày %d Tháng %d Năm %d", 
					DateUtil.getDayOfMonth(toDate),
					DateUtil.getMonth(toDate) + 1,
					DateUtil.getYear(toDate))));
			cell.setCellStyle(styleCenter_I12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 10, 12));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Trưởng Bộ Phận"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 1, 3));
			
			cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Người lập"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 10, 12));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("(Ký, họ tên)"));
			cell.setCellStyle(styleCenter_I10);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 1, 3));
			
			cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("(Ký, họ tên)"));
			cell.setCellStyle(styleCenter_I10);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 10, 12));
			
			r = r + 4;
			row = sheet.createRow(r);
			row.setHeightInPoints(15);
			
			cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getLep()));
			cell.setCellStyle(styleCenter_BI12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 1, 3));
			
			cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getBsr()));
			cell.setCellStyle(styleCenter_BI12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 10, 12));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}
	
	public String reportImportExport() {
		Date fromDate = DateUtil.getStartDateOfMonth(month, year);
		Date toDate = DateUtil.getEndDateOfMonth(month, year);
		
		ImportExportReport report = new ImportExportReport(fromDate, toDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Import-Export Report - " + this.month + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold16 = ExcelUtil.makeFont(workbook, "Times New Roman", 16, true);
			HSSFFont fontBold13 = ExcelUtil.makeFont(workbook, "Times New Roman", 13, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont fontBold11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, true);
			
			HSSFFont fontNormal12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			
			HSSFFont fontItalic12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			fontItalic12.setItalic(true);
			
			HSSFFont fontBoldItalic12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			fontBoldItalic12.setItalic(true);
			
			HSSFFont fontItalic10 = ExcelUtil.makeFont(workbook, "Times New Roman", 10, false);
			fontItalic10.setItalic(true);
			
			HSSFCellStyle styleCenter_B16 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold16);
			HSSFCellStyle styleCenter_B13 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold13);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			
			HSSFCellStyle styleCenter_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			HSSFCellStyle styleLeft_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			HSSFCellStyle styleRight_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			
			HSSFCellStyle styleCenter_B11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold11);
			HSSFCellStyle styleLeft_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			HSSFCellStyle styleCenter_BI12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBoldItalic12);
			
			HSSFCellStyle styleCenter_I12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontItalic12);
			HSSFCellStyle styleCenter_I10 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontItalic10);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B16);
			row.setHeightInPoints(21);
			cell.setCellValue(new HSSFRichTextString("TỔNG HỢP XUẤT NHẬP TỒN"));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("Cơ Sở: Quang Trung"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString(String.format("Từ %s Đến %s", report.getFromDate(), report.getToDate())));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "STT", 256*6, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "Mã sách", 256*13, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "Tài liệu", 256*40, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "Loại tài liệu", 256*15, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "Đvt", 256*10, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "Tồn đầu kỳ", 256*12, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "Nhập", 256*11, styleCenter_B13);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 2, 2));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 3, 3));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 4, 4));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 5, 5));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 6, 6));
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 10, "Trả", 256*11, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 11, "Tồn cuối kỳ", 256*12, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 12, "Ghi chú", 256*26, styleCenter_B13);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 10, 10));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 11, 11));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 12, 12));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "Xuất", 256*10, styleCenter_B13);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 9));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(20);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "Số lượng", 256*12, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 8, "Giá bán", 256*11, styleCenter_B13);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 9, "Thành tiền", 256*13, styleCenter_B13);
			
			int bookFirstRow = r + 2;
			List<ImportExportDetail> bookDetails = report.getBookReportDetails();
			int index = 1;
			for(int i = 0; i < bookDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(15);
				
				ImportExportDetail detail = bookDetails.get(i);
				int mergeRowSize = detail.getPriceMap().size();
				if(mergeRowSize == 0) {
					mergeRowSize = 1;
				}
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(index);
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 0, 0));
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemId()));
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 1, 1));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemTitle().getTitle()));
				cell.setCellStyle(styleLeft_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 2, 2));
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemType().getName()));
				cell.setCellStyle(styleLeft_N11);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 3, 3));
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 4, 4));
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getFirstRemain());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 5, 5));
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getSumOfImport());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 6, 6));
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getSumOfReturn());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 10, 10));
				
				cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getLastRemain());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 10, 10));
				
				cell = row.createCell(12, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString());
				cell.setCellStyle(styleLeft_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 11, 11));
				
				List<Entry<Double, Integer>> prices = new ArrayList<Entry<Double,Integer>>(detail.getPriceMap().entrySet());
				for(int j = 0; j < prices.size(); j++) {
					Entry<Double, Integer> entry = prices.get(j);
					if(j != 0) {
						r = r + 1;
						row = sheet.createRow(r);
					}
					cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleCenter_N12);
					cell.setCellValue(entry.getValue());
					
					cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleCenter_N12);
					cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
					cell.setCellValue(entry.getKey());
					
					cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleRight_N12);
					cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
					cell.setCellValue(entry.getKey() * entry.getValue());
				}
				index++;
			}
			
			int bookLastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Tổng cộng số lượng sách"));
			cell.setCellStyle(styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(F" + bookFirstRow + ":"  + "F" + bookLastRow + ")");
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(G" + bookFirstRow + ":"  + "G" + bookLastRow + ")");
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(H" + bookFirstRow + ":"  + "H" + bookLastRow + ")");
			
			cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			cell.setCellFormula("SUM(J" + bookFirstRow + ":"  + "J" + bookLastRow + ")");
			
			cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(K" + bookFirstRow + ":"  + "K" + bookLastRow + ")");
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(L" + bookFirstRow + ":"  + "L" + bookLastRow + ")");
			
			// CD
			int cdFirstRow = r + 2;
			List<ImportExportDetail> cdDetails = report.getCdReportDetails();
			for(int i = 0; i < cdDetails.size(); i++) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(15);
				
				ImportExportDetail detail = cdDetails.get(i);
				int mergeRowSize = detail.getPriceMap().size();
				if(mergeRowSize == 0) {
					mergeRowSize = 1;
				}
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(index);
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 0, 0));
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemId()));
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 1, 1));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemTitle().getTitle()));
				cell.setCellStyle(styleLeft_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 2, 2));
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemType().getName()));
				cell.setCellStyle(styleLeft_N11);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 3, 3));
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString(detail.getItem().getItemType().getUnit()));
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 4, 4));
				
				cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getFirstRemain());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 5, 5));
				
				cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getSumOfImport());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 6, 6));
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getSumOfReturn());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 10, 10));
				
				cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellValue(detail.getLastRemain());
				cell.setCellStyle(styleCenter_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 10, 10));
				
				cell = row.createCell(12, HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(new HSSFRichTextString());
				cell.setCellStyle(styleLeft_N12);
				sheet.addMergedRegion(new CellRangeAddress(r, r + mergeRowSize - 1, 11, 11));
				
				List<Entry<Double, Integer>> prices = new ArrayList<Entry<Double,Integer>>(detail.getPriceMap().entrySet());
				for(int j = 0; j < prices.size(); j++) {
					Entry<Double, Integer> entry = prices.get(j);
					if(j != 0) {
						r = r + 1;
						row = sheet.createRow(r);
					}
					cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleCenter_N12);
					cell.setCellValue(entry.getValue());
					
					cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleCenter_N12);
					cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
					cell.setCellValue(entry.getKey());
					
					cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleRight_N12);
					cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
					cell.setCellValue(entry.getKey() * entry.getValue());
				}
				index++;
			}
			
			int cdLastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Tổng cộng số lượng đĩa"));
			cell.setCellStyle(styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(F" + cdFirstRow + ":"  + "F" + cdLastRow + ")");
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(G" + cdFirstRow + ":"  + "G" + cdLastRow + ")");
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(H" + cdFirstRow + ":"  + "H" + cdLastRow + ")");
			
			cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			cell.setCellFormula("SUM(J" + cdFirstRow + ":"  + "J" + cdLastRow + ")");

			cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellValue(1);
			cell.setCellFormula("SUM(K" + cdFirstRow + ":"  + "K" + cdLastRow + ")");
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("SUM(L" + cdFirstRow + ":"  + "L" + cdLastRow + ")");
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Tổng cộng"));
			cell.setCellStyle(styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("F" + (bookLastRow + 1) + "+"  + "F" + (cdLastRow + 1));
			
			cell = row.createCell(6, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("G" + (bookLastRow + 1) + "+"  + "G" + (cdLastRow + 1));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("H" + (bookLastRow + 1) + "+"  + "H" + (cdLastRow + 1));
			
			cell = row.createCell(9, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("J" + (bookLastRow + 1) + "+"  + "J" + (cdLastRow + 1));
			
			cell = row.createCell(10, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("K" + (bookLastRow + 1) + "+"  + "K" + (cdLastRow + 1));
			
			cell = row.createCell(11, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B11);
			cell.setCellFormula("L" + (bookLastRow + 1) + "+"  + "L" + (cdLastRow + 1));

			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(String.format("Ngày %d Tháng %d Năm %d", 
					DateUtil.getDayOfMonth(toDate),
					DateUtil.getMonth(toDate) + 1,
					DateUtil.getYear(toDate))));
			cell.setCellStyle(styleCenter_I12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 9));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Trưởng Bộ phận"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Kế toán"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("Người lập"));
			cell.setCellStyle(styleCenter_B12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 9));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("(Ký, họ tên)"));
			cell.setCellStyle(styleCenter_I10);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString("(Ký, họ tên)"));
			cell.setCellStyle(styleCenter_I10);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 9));
			
			r = r + 4;
			row = sheet.createRow(r);
			row.setHeightInPoints(15);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getLep()));
			cell.setCellStyle(styleCenter_BI12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 2));
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getAcc()));
			cell.setCellStyle(styleCenter_BI12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(report.getBsr()));
			cell.setCellStyle(styleCenter_BI12);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 9));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}
	
	public String reportDaily() {
		Date dateDate = DateUtil.fromString(this.date, this.i18n("format.date"));
		int day = DateUtil.getField(dateDate, Calendar.DAY_OF_MONTH);
		int month = DateUtil.getField(dateDate, Calendar.MONTH);
		int year = DateUtil.getField(dateDate, Calendar.YEAR);
		
		List<Order> orders = OrderService.INSTANCE.findByDateAsc(dateDate, dateDate);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Bookstore Report - " + (month + 1) + ".xls\"");
		
		try {
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Report");
			
			HSSFFont fontBold16 = ExcelUtil.makeFont(workbook, "Times New Roman", 16, true);
			HSSFFont fontBold12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, true);
			HSSFFont fontBold11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, true);
			
			HSSFFont fontNormal12 = ExcelUtil.makeFont(workbook, "Times New Roman", 12, false);
			HSSFFont fontNormal11 = ExcelUtil.makeFont(workbook, "Times New Roman", 11, false);
			
			HSSFCellStyle styleCenter_B16 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold16);
			HSSFCellStyle styleCenter_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			HSSFCellStyle styleCenter_B11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontBold11);
			
			HSSFCellStyle styleRight_B12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontBold12);
			
			HSSFCellStyle styleCenter_N12 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal12);
			HSSFCellStyle styleCenter_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_CENTER, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleLeft_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_LEFT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			HSSFCellStyle styleRight_N11 = ExcelUtil.makeCellStyle(workbook, HSSFCellStyle.ALIGN_RIGHT, HSSFCellStyle.VERTICAL_CENTER, fontNormal11);
			
			int r = 0;
			HSSFRow row = sheet.createRow(r);
			
			HSSFCell cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B16);
			row.setHeightInPoints(21);
			cell.setCellValue(new HSSFRichTextString("BẢNG KÊ CHI TIẾT NỘP TIỀN SÁCH ĐĨA THÁNG " + (month + 1) + "/" + year));
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("Cơ Sở: Quang Trung"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(16);
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Ngày " + DateUtil.toString(dateDate, "dd/MM/yyyy")));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 10));
			
			// BLANK
			r = r + 1;
			row = sheet.createRow(r);
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(14);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 0, "STT", 256*5, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 1, "NGÀY", 256*11, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 2, "SỐ BIÊN LAI", 256*16, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 3, "HỌ TÊN", 256*30, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 4, "LOẠI SÁCH", 256*23, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 2, 2));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 3, 3));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 4, 4));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 9, "GHI CHÚ", 256*14, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 10, "NGƯỜI THU", 256*12, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 9, 9));
			sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 10, 10));
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "SỐ TIỀN", 256*11, styleCenter_B11);
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 8));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(14);
			
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 5, "SB", 256*12, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 6, "WB", 256*12, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 7, "CD", 256*12, styleCenter_B11);
			ExcelUtil.makeColumnLabel(workbook, sheet, row, 8, "TỔNG CỘNG", 256*11, styleCenter_B11);
			
			int firstRow = r + 2;
			
			int index = 1;
			for(Order order : orders) {
				r = r + 1;
				row = sheet.createRow(r);
				row.setHeightInPoints(18);
				
				cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(index);

				Date date = order.getDate();
				String value = DateUtil.getDayOfMonth(date) + "-" + DateUtil.getMonthAsString(date) + "-" + new String(year + "").substring(2, 4);
				
				cell = row.createCell(1, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(value));
				
				cell = row.createCell(2, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(order.getNumber()));
				
				cell = row.createCell(3, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleLeft_N11);
				cell.setCellValue(new HSSFRichTextString(order.getStudentName()));
				
				List<ItemTitle> titles = order.getItemTitles();
				StringBuffer buffer = new StringBuffer();
				for(int i = 0; i < titles.size(); i++) {
					if(i == (titles.size() - 1)) {
						buffer.append(titles.get(i).getTitle());
					} else
						buffer.append(titles.get(i).getTitle() + ", ");
				}
				
				cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(buffer.toString()));
				
				Map<ItemType, Double> map = order.getPriceMap();
				int acc = 5;
				
				Collection<ItemType> types = ItemTypeService.INSTANCE.findAll();
				for(ItemType type : types) {
					cell = row.createCell(acc, HSSFCell.CELL_TYPE_NUMERIC);
					cell.setCellStyle(styleRight_N11);
					cell.setCellValue(map.get(type));
					cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
					acc++;
				}
				cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
				cell.setCellStyle(styleRight_N11);
				cell.setCellValue(order.getTotalPrice().getAmount());
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
				
				cell = row.createCell(10, HSSFCell.CELL_TYPE_STRING);
				cell.setCellStyle(styleCenter_N11);
				cell.setCellValue(new HSSFRichTextString(order.getCashier().getFirstName()));
			}
			int lastRow = r + 1;
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(18);
			
			cell = row.createCell(0, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("TỔNG CỘNG"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 0, 4));
			
			cell = row.createCell(5, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleCenter_B12);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(F" + firstRow + ":"  + "F" + lastRow + ") + " + "SUM(G" + firstRow + ":"  + "G" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
			
			cell = row.createCell(7, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleRight_B12);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(H" + firstRow + ":"  + "H" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			cell = row.createCell(8, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellStyle(styleRight_B12);
			if(lastRow >= firstRow) {
				cell.setCellFormula("SUM(I" + firstRow + ":"  + "I" + lastRow + ")");
				cell.getCellStyle().setDataFormat(workbook.createDataFormat().getFormat("#,##0"));
			}
			
			r = r + 2;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Tp.Hồ Chí Minh, ngày " + day + " tháng " + (month + 1) + " năm " + year));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 10));
			
			r = r + 1;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_N12);
			cell.setCellValue(new HSSFRichTextString("Người nộp"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 10));
			
			r = r + 5;
			row = sheet.createRow(r);
			row.setHeightInPoints(17);
			
			cell = row.createCell(4, HSSFCell.CELL_TYPE_STRING);
			cell.setCellStyle(styleCenter_B12);
			cell.setCellValue(new HSSFRichTextString("Lê Thị Thanh Nhung"));
			sheet.addMergedRegion(new CellRangeAddress(r, r, 4, 10));
			
			workbook.write(RESPONSE().getOutputStream());
		} catch(Exception ex) {
			ex.printStackTrace();
		}
		return NONE;
	}

	public String getDate() {
		return date;
	}

	public void setDate(String date) {
		this.date = date;
	}

	public int getMonth() {
		return month;
	}

	public void setMonth(int month) {
		this.month = month;
	}

	public int getYear() {
		return year;
	}

	public void setYear(int year) {
		this.year = year;
	}
}
